package com.qf.base.utils.excelUtils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class ExcelSheetsUtil {
    //导出表的列名
    private String[] rowName;

    //导出表的头部标题
    private String oneheaders;

    //sheet表表名
    private String sheettitle;

    // 需要导出的数据集合
    private List<Object[]> dataList;

    /**
     * @param sheettitle 单个sheet名称
     * @param oneheaders 单个sheet头部名称
     * @param rowName  单个sheet对应的列头
     * @param dataList  单个sheet对应的数据
     */
    public ExcelSheetsUtil(String sheettitle, String oneheaders, String[] rowName, List<Object[]> dataList){
        this.dataList = dataList;
        this.oneheaders = oneheaders;
        this.rowName = rowName;
        this.sheettitle = sheettitle;
    }

    /*
     * 导出数据
     * */
    public void export(HSSFWorkbook workbook) throws Exception{
        try{
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet(sheettitle);

            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);

            // 定义所需列数
            int columnNum = rowName.length;

            /***   绑定列头   ****/
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(0);
            //单元格样式对象
            HSSFCellStyle styleHearer = this.getColumnTopStyle(workbook);
            for(int n = 0; n < columnNum; n++){
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(styleHearer);
            }


            /***   绑定数据   ****/
            //	将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i<dataList.size();i++){
                Object[] obj = dataList.get(i);
                //	光路数据执行跨行逻辑
                if(rowName[1].equals("光路名称")){
                    //	定义下一行的数据、并且处理最后末行的逻辑
                    Object[] obj_next = null;
                    if(i+1 < dataList.size()){
                        try {
                            obj_next = dataList.get(i+1);
                            //	如果地区和光路名称完全一致、则进行合并处理
                            if(obj_next != null && obj != null){
                                if( null != obj[0] && null != obj[1] && null != obj_next[0] && null != obj_next[1] &&  obj[0].equals(obj_next[0]) && obj[1].equals(obj_next[1])){//地区和光路名称完全一致的情况进行单元格合并
                                    // index 是一条光路跨行的数量，由地区+光路名称进行区分的、只要多条数据的区域+光路名称相同、系统则认为是一条数据（其他字段跨行不能超过此字段）
                                    int index = getAcrossRowNum(String.valueOf(obj[0]),String.valueOf(obj[1]), i,dataList ,0);
                                    sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+index, 0, 0));
                                    sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+index, 1, 1));

                                    if( null != obj[2] && obj[2].equals(obj_next[2])){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[2]),"", i,dataList ,2)>index?index:getAcrossRowNum(String.valueOf(obj[2]),"", i,dataList ,2)), 2, 2));
                                    }else{
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+index, 2, 2));//	宽带为空的数据
                                    }
                                    if( null != obj[3] && obj[3].equals(obj_next[3])){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[3]),"", i,dataList ,3)>index?index:getAcrossRowNum(String.valueOf(obj[3]),"", i,dataList ,3)), 3, 3));
                                    }
                                    if( null != obj[4] && obj[4].equals(obj_next[4])){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[4]),"", i,dataList ,4)>index?index:getAcrossRowNum(String.valueOf(obj[4]),"", i,dataList ,4)), 4, 4));
                                    }
                                    if( null != obj[5] && obj[5].equals(obj_next[5]) ){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[5]),"", i,dataList ,5)>index?index:getAcrossRowNum(String.valueOf(obj[5]),"", i,dataList ,5)), 5, 5));
                                    }
                                    if( null != obj[6] && obj[6].equals(obj_next[6]) ){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[6]),"", i,dataList ,6)>index?index:getAcrossRowNum(String.valueOf(obj[6]),"", i,dataList ,6)), 6, 6));
                                    }
                                    if( null != obj[7] && obj[7].equals(obj_next[7]) ){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[7]),"", i,dataList ,7)>index?index:getAcrossRowNum(String.valueOf(obj[7]),"", i,dataList ,7)), 7, 7));
                                    }
                                    if( null != obj[8] && obj[8].equals(obj_next[8]) ){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[8]),"", i,dataList ,8)>index?index:getAcrossRowNum(String.valueOf(obj[8]),"", i,dataList ,8)), 8, 8));
                                    }
                                    if( null != obj[9] && obj[9].equals(obj_next[9]) ){
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[9]),"", i,dataList ,9)>index?index:getAcrossRowNum(String.valueOf(obj[9]),"", i,dataList ,9)), 9, 9));
                                    }
                                }else if( null != obj[20]){//根据数据来源区分
                                    if(String.valueOf(obj[20]).equals("TMS光路信息") && null != obj[1] && null != obj_next[1] && obj[1].equals(obj_next[1])){
                                        int index = getAcrossRowNum(String.valueOf(obj[1]), i,dataList ,0);
                                        sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+index, 0, 0));//	区域为空的数据
                                        if( null != obj[1] && obj[1].equals(obj_next[1])){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[1]),"", i,dataList ,1)>index?index:getAcrossRowNum(String.valueOf(obj[1]),"", i,dataList ,1)), 1, 1));
                                        }
                                        if( null != obj[2] && obj[2].equals(obj_next[2])){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[2]),"", i,dataList ,2)>index?index:getAcrossRowNum(String.valueOf(obj[2]),"", i,dataList ,2)), 2, 2));
                                        }else{
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+index, 2, 2));//	宽带为空的数据
                                        }
                                        if( null != obj[3] && obj[3].equals(obj_next[3])){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[3]),"", i,dataList ,3)>index?index:getAcrossRowNum(String.valueOf(obj[3]),"", i,dataList ,3)), 3, 3));
                                        }
                                        if( null != obj[4] && obj[4].equals(obj_next[4])){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[4]),"", i,dataList ,4)>index?index:getAcrossRowNum(String.valueOf(obj[4]),"", i,dataList ,4)), 4, 4));
                                        }
                                        if( null != obj[5] && obj[5].equals(obj_next[5]) ){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[5]),"", i,dataList ,5)>index?index:getAcrossRowNum(String.valueOf(obj[5]),"", i,dataList ,5)), 5, 5));
                                        }
                                        if( null != obj[6] && obj[6].equals(obj_next[6]) ){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[6]),"", i,dataList ,6)>index?index:getAcrossRowNum(String.valueOf(obj[6]),"", i,dataList ,6)), 6, 6));
                                        }
                                        if( null != obj[7] && obj[7].equals(obj_next[7]) ){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[7]),"", i,dataList ,7)>index?index:getAcrossRowNum(String.valueOf(obj[7]),"", i,dataList ,7)), 7, 7));
                                        }
                                        if( null != obj[8] && obj[8].equals(obj_next[8]) ){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[8]),"", i,dataList ,8)>index?index:getAcrossRowNum(String.valueOf(obj[8]),"", i,dataList ,8)), 8, 8));
                                        }
                                        if( null != obj[9] && obj[9].equals(obj_next[9]) ){
                                            sheet.addMergedRegion(new CellRangeAddress(i+1, i+1+(getAcrossRowNum(String.valueOf(obj[9]),"", i,dataList ,9)>index?index:getAcrossRowNum(String.valueOf(obj[9]),"", i,dataList ,9)), 9, 9));
                                        }
                                    }

                                }
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
                //创建所需的行数（从第二行开始写数据）
                HSSFRow row = sheet.createRow(i+1);

                for(int j = 0; j < obj.length; j++){
                    //设置单元格的数据类型
                    HSSFCell  cell = row.createCell(j);

                    if(!"".equals(obj[j]) && obj[j] != null){

                        //设置单元格的值
                        cell.setCellValue(obj[j].toString());
                    }
                    //设置单元格样式
                    switch (j) {
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                        case 6:
                        case 7:
                        case 18:
                        case 11:
                        case 17:
                        case 19:
                            sheet.setColumnWidth(j, 6000);
                            break;
                        case 5:
                        case 8:
                            sheet.setColumnWidth(j, 4000);
                            break;
                        case 9:
                        case 10:
                            sheet.setColumnWidth(j, 8000);
                            break;
                        case 0:
                        case 12:
                        case 14:
                        case 15:
                        case 16:
                        case 13:
                            sheet.setColumnWidth(j, 3000);
                            break;
                        default:
                            break;
                    }
                    cell.setCellStyle(style);
                }


            }

        }catch(Exception e){
            e.printStackTrace();
        }
    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)14);
        //字体加粗
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)14);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
    /**
     * 合并行的数量计算
     * @param content1
     * @param content2
     * @param i	下标
     * @param list 数据
     * @param cell 当前列
     * @return
     */
    public int getAcrossRowNum(String content1,String content2,int i,List<Object[]> list,int cell){
        int index = 0;
        for (int j = i+1; j < list.size(); j++) {
            if(!content2.equals("")){
                if(content1.equals(list.get(j)[cell]) && content2.equals(list.get(j)[cell+1])){
                    index++;
                }else{
                    break;
                }
            }else{
                if(content1.equals(list.get(j)[cell])){
                    index++;
                }else{
                    break;
                }
            }
        }
        return index;
    }
    public int getAcrossRowNum(String content2, int i, List<Object[]> list, int cell){
        int index = 0;
        for (int j = i+1; j < list.size(); j++) {
            if(!content2.equals("")){
                if(content2.equals(list.get(j)[cell+1])){
                    index++;
                }else{
                    break;
                }
            }
        }
        return index;
    }
}
